﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

using SwenAssigment.Classes;
using System.Collections;

namespace SwenAssigment.Classes
{
    public class RADcon
    {
        public static SqlConnection thecon()
        {
            SqlConnection mycon = new SqlConnection();
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            mycon.ConnectionString = constr;
            return mycon;
        }
        public ArrayList raretrieve(string linecom)
        {
            try
            {
                ArrayList al = new ArrayList();
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Room r = new Room();
                    r.roomid = (int)reader["Room_id"];
                    r.location = (string)reader["Location"];
                    r.desc = (string)reader["Description"];
                    r.costperday = (decimal)reader["Cost_Per_Day"];
                    r.status = (string)reader["Status"];
                    al.Add(r);
                }
                reader.Close();
                mycon.Close();
                return al;
            }
            catch
            {
                return null;
            }
        }
        public string[] raretrieveone(string linecom)
        {
            try
            {
                string[] s = new string[5];
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
                s[0] = (string)reader["Room_id"].ToString();
                s[1] = (string)reader["Location"];
                s[2] = (string)reader["Description"];
                s[3] = (string)reader["Cost_Per_Day"].ToString();
                s[4] = (string)reader["Status"];
                reader.Close();
                mycon.Close();
                return s;
            }
            catch
            {
                return null;
            }
        }
        public bool raupdate(int id, decimal cost, string status)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "update room set Cost_Per_Day='" + cost + "',Status='" + status + "' where Room_ID='" + id + "'";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public ArrayList gretrieve(string linecom)
        {
            try
            {
                ArrayList al = new ArrayList();
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Guest g = new Guest();
                    g.guestid = (int)reader["Guest_ID"];
                    g.fname = (string)reader["First_Name"];
                    g.lname = (string)reader["Last_Name"];
                    g.contact = (int)reader["Contact_Number"];
                    g.email = (string)reader["Email"];
                    g.address = (string)reader["Address"];
                    g.ic = (string)reader["Identification_Code"];
                    al.Add(g);
                }
                reader.Close();
                mycon.Close();
                return al;
            }
            catch
            {
                return null;
            }
        }
        public string[] grestrieveone(string linecom)
        {
            try
            {
                string[] s = new string[7];
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
                s[0] = (string)reader["Guest_ID"].ToString();
                s[1] = (string)reader["First_Name"];
                s[2] = (string)reader["Last_Name"];
                s[3] = (string)reader["Contact_Number"].ToString();
                s[4] = (string)reader["Email"];
                s[5] = (string)reader["Address"];
                s[6] = (string)reader["Identification_Code"];
                reader.Close();
                mycon.Close();
                return s;
            }
            catch
            {
                return null;
            }
        }
        public bool gcreate(Guest g)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "insert into guest (First_Name,Last_Name,Contact_Number,Email,Address,Identification_Code) values ('" + g.fname + "','" + g.lname + "','" + g.contact + "','" + g.email + "','" + g.address + "','" + g.ic + "')";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool gupdate(int id, int contact, string email, string address)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "update Guest set Contact_Number='" + contact + "',Email='" + email + "',Address='" + address + "' where Guest_ID='" + id + "'";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public ArrayList bretrieve(string linecom)
        {
            try
            {
                ArrayList al = new ArrayList();
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    Booking b = new Booking();
                    b.bookid = (int)reader["Room_Booking_ID"];
                    b.guestid = (int)reader["Guest_ID"];
                    b.numadult = (int)reader["Number_Of_Adults"];
                    b.numchild = (int)reader["Number_Of_Children"];
                    b.checkindt = (string)reader["Checkin_DateTime"].ToString();
                    b.pcheckoutdt = (string)reader["Prefer_CheckOut_DateTime"].ToString();
                    b.proomtype = (string)reader["Preferred_Room_Type"];
                    b.remark = (string)reader["Additional_Remarks"];
                    b.latecheckout = (bool)reader["Option_Late_CheckOut"];
                    b.roomassign = (int)reader["Room_Assigned"];
                    b.actualcheckout = (string)reader["Actual_CheckOut_DateTime"].ToString();
                    al.Add(b);
                }
                reader.Close();
                mycon.Close();
                return al;
            }
            catch
            {
                return null;
            }
        }
        public string[] bretrieveone(string linecom)
        {
            try
            {
                string[] s = new string[11];
                SqlConnection mycon = thecon();
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
                s[0] = (string)reader["Room_Booking_ID"].ToString();
                s[1] = (string)reader["Guest_ID"].ToString();
                s[2] = (string)reader["Number_Of_Adults"].ToString();
                s[3] = (string)reader["Number_Of_Children"].ToString();
                s[4] = (string)reader["Checkin_DateTime"].ToString();
                s[5] = (string)reader["Prefer_CheckOut_DateTime"].ToString();
                s[6] = (string)reader["Preferred_Room_Type"].ToString();
                s[7] = (string)reader["Additional_Remarks"];
                s[8] = (string)reader["Option_Late_CheckOut"].ToString();
                s[9] = (string)reader["Room_Assigned"].ToString();
                s[10] = (string)reader["Actual_CheckOut_DateTime"].ToString();
                reader.Close();
                mycon.Close();
                return s;
            }
            catch
            {
                return null;
            }
        }
        public bool bupdate(int id, int ad, int ch)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "update Room_Booking set Number_Of_Adults='" + ad + "',Number_Of_Children='" + ch + "' where Room_Booking_ID='" + id + "'";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool bupdateco(int id, string actual, bool bl)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "update Room_Booking set Additional_Remarks='Out',Option_Late_CheckOut='" + bl + "',Actual_CheckOut_DateTime=Convert(Date,'" + actual + "',104) where Room_Booking_ID='" + id + "'";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool bcreate(Booking b)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "insert into Room_Booking (Guest_ID,Number_Of_Adults,Number_Of_Children,Checkin_DateTime,Prefer_CheckOut_DateTime,Preferred_Room_Type,Room_Assigned) values ('" + b.guestid + "','" + b.numadult + "','" + b.numchild + "',Convert(Date,'" + b.checkindt + "',104),Convert(Date,'" + b.pcheckoutdt + "',104),'" + b.proomtype + "','" + b.roomassign + "')";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool pcreate(Pay p)
        {
            try
            {
                SqlConnection mycon = thecon();
                string linecom = "insert into Payment (Room_Booking_ID,Additional_Cost,Content_Number,Payment_Detail,Handle_By,Others) values ('" + p.bookid + "','" + p.addcost + "','" + p.content + "','" + p.paydetail + "','" + p.handleby + "','" + p.other + "')";
                SqlCommand com = new SqlCommand(linecom, mycon);
                mycon.Open();
                com.ExecuteNonQuery();
                mycon.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}